#coding=utf-8
#@Time:2019/12/11 6:59 PM
#@Author:希哥
#@File:sql.py

import mysql_getData

'''
    单个结果查询
'''
def search(sql,venv):
    try:
        if venv == 'local':
            connect = mysql_getData.mysql_connect()[0]  # connect是由connect_mysql函数返回的第一个值
            cursor = connect.cursor()
            # 执行sql语句
            cursor.execute(sql)
            result = cursor.fetchall()
            connect.commit()  # 实例提交命令
            final_result = result[0][0]
            return str(final_result)
        if venv == 'hupo_test':
            connect = mysql_getData.mysql_connect_hupotest()[0]  # connect是由connect_mysql函数返回的第一个值
            cursor = connect.cursor()
            # 执行sql语句
            cursor.execute(sql)
            result = cursor.fetchall()
            connect.commit()  # 实例提交命令
            final_result = result[0][0]
            return str(final_result)
        if venv == 'hupo_online':
            connect = mysql_getData.mysql_connect_hupomallnew()[0]  # connect是由connect_mysql函数返回的第一个值
            cursor = connect.cursor()
            # 执行sql语句
            cursor.execute(sql)
            result = cursor.fetchall()
            connect.commit()  # 实例提交命令
            final_result = result[0][0]
            return str(final_result)
    except:
        return 'None'

'''
    多个结果查询
'''
def search_total(sql,venv):
    if venv == 'local':
        connect = mysql_getData.mysql_connect()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        result = cursor.fetchall()
        connect.commit()  # 实例提交命令
        final_result = result
        return final_result  #返回全部查询结果
    if venv == 'hupo_test':
        connect = mysql_getData.mysql_connect_hupotest()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        result = cursor.fetchall()
        connect.commit()  # 实例提交命令
        final_result = result
        return final_result  # 返回全部查询结果
    if venv == 'hupo_online':
        connect = mysql_getData.mysql_connect_hupomallnew()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        result = cursor.fetchall()
        connect.commit()  # 实例提交命令
        final_result = result
        return final_result  # 返回全部查询结果

'''
    更新数据库
'''
def update(sql,venv):
    if venv == 'local':
        connect = mysql_getData.mysql_connect()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        connect.commit()  # 实例提交命令
    if venv == 'hupo_test':
        connect = mysql_getData.mysql_connect_hupotest()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        connect.commit()  # 实例提交命令
    if venv == 'hupo_online':
        connect = mysql_getData.mysql_connect_hupomallnew()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        connect.commit()  # 实例提交命令

'''
    写入数据
'''
def insert(sql,venv):
    if venv == 'local':
        connect = mysql_getData.mysql_connect()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        connect.commit()  # 实例提交命令
    if venv == 'hupo_test':
        connect = mysql_getData.mysql_connect_hupotest()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        connect.commit()  # 实例提交命令
    if venv == 'hupo_online':
        connect = mysql_getData.mysql_connect_hupomallnew()[0]  # connect是由connect_mysql函数返回的第一个值
        cursor = connect.cursor()
        # 执行sql语句
        cursor.execute(sql)
        connect.commit()  # 实例提交命令

if __name__=='__main__':
    r = search_total("SELECT ctu.coupon_code FROM coupon_to_user as ctu INNER JOIN hp_gift_pack.gift_pack_to_gift as gptp ON ctu.coupon_config_id = gptp.gift_id INNER JOIN hp_gift_pack.gift_pack as gp ON gp.id = gptp.gift_pack_id WHERE gp.title = '樊登小读者六一礼包' AND ctu.user_id = (SELECT id FROM `user` WHERE mobile = '17781108472' AND user_type = 'normal') GROUP BY ctu.coupon_code", 'hupo_online')
    print r
    if r == []:
        print True
    else:
        print False